package de.hmm.hwip.storage.service;

import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.json.JSONException;
import org.json.JSONObject;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;
import de.hmm.hwip.general.IMessageContent;

/**
 *  Storage Thread
 * 	saves, read, update and delete Data in Database  
 * 
 * @author johannes mendel
 * @version 17.10.2012
 *
 */
public class SQLiteStorageThread implements Runnable{
	
	/**Class TAG **/
	private final String TAG = "SQLiteStorageThread";
	
	/**Context from Android Service **/
	private Context androidContext;
	
	/** Connection to the SQL Database **/
	private SQLiteDatabase connection;
	
	/**Helperclass for Database connection **/
	private SQLiteOpenHelper database;
	
	/** SQL query for databse commands **/
	private String query;
	
	/** used if multiple datas are inserted to database **/
	private Map<String, Object> completeDb;
	
	/** type of Query (1 = Select, 2 = insert / apdate, 3 = multiinsert) **/
	private int queryType;
	
	/** Message Content which contains all parameters **/ 
	private IMessageContent msgContent;
	
	/**Cursor with collected data from database **/
	Cursor result;
	
	/**
	 * set all parameters which are needed for the query
	 * @param content - source where parameters come from and go to
	 */
	public void setContentData(IMessageContent content)
	{
		this.query = content.getQuery();
		this.queryType = content.getQueryType();
		this.msgContent = content;
		this.completeDb = content.getCompleteDB();
	}

	@Override
	public void run() {
		androidContext = Activator.getAndroidContext();
		
		Log.i(TAG, "SQLite Storage Thread started");
		synchronized(this){
			while(androidContext == null){
				try{
					Log.i(TAG, "wait 2 seconds for android Context");
					wait(2000);
					androidContext = Activator.getAndroidContext();
				}catch (Exception e){
					Log.e(TAG, "Synchronized exception: " + e.getMessage());
				}
			}
		}
		
		database = new HWipDatabaseHelper(androidContext);
		connection = database.getWritableDatabase();
		
		// check which type of query is used - select or update/insert
		if(queryType == 1){
			Log.i(TAG, "Get Data from Database: " + query);
			getData();
		}else if(queryType == 2){
			Log.i(TAG, "Set Data from Database: " + query);
			setData();
		}else if(queryType == 3){
			Log.i(TAG, "Set complete Database");
			setCompleteDb();
		}else if(queryType == 4){
			Log.i(TAG, "Get complete Database");
			getCompleteDB();
		}
		
		//datas are collected ... go on
		this.msgContent.stopWaiting();
		connection.close();
	}
	
	/**
	 * update or insert data into database
	 * @return 1 if ok
	 */
	private int setData(){
		try{
			connection.execSQL(query);
			this.msgContent.setQueryResult(1);
	
		}catch(SQLException e){
			Log.e(TAG, "Writing data to Database failed: " + e.getMessage() );
			this.msgContent.setErrorResult("Writing data to Database failed");
			this.msgContent.stopWaiting();
		}
		
		return 1;
	}
	
	/**
	 * select data from database
	 */
	private void getData(){
		result = connection.rawQuery(query, null);

		if(result != null){
			this.msgContent.setQueryResult(result);
		}else
			this.msgContent.setQueryResult(null);
	
		Log.i(TAG, "result: " + result.getCount());
		//result.close();
	}
	
	/**
	 * splits Map of tables and rout them to "setMultipleData"
	 * @return 1 if OK
	 */
	@SuppressWarnings("unchecked")
	private int setCompleteDb(){
		int ok = 0;
		
		try{
			Set<String> tableset = completeDb.keySet();
			  
		    for(String t : tableset){
		    	connection.delete(t, null, null);
		    	ok = setMultipleData(t, (Map<String,Object>) completeDb.get(t));
		    }
		}catch(SQLException e){
			Log.e(TAG, "Writing data to Database failed: " + e.getMessage() );
			this.msgContent.setErrorResult("Writing data to Database failed");
			this.msgContent.stopWaiting();
		}
		
		return ok;
	}
	
	/**
	 * inserts multiple Data to table given by tableName and Map of Table columns.
	 * @param table = <Column, Value[]>
	 * @param tableName = name of Table (String)
	 * @return count of dataset inserted 
	 */
	@SuppressWarnings("rawtypes")
	private int setMultipleData(String tableName, Map<String, Object> table) throws SQLException{
		int count = 0;
		JSONObject columnvalues;
		String colNames = "" , colCount = "";
		SQLiteStatement insertStmnt = null;
		String column;
		boolean firstCol = true, setStmnt = true, firstLine = false;
		try{
			for(int i = 0; i <= table.size() ; i++ ){
				if(firstLine && i == 1){
					i = 0;
				}
				if(i < table.size()){
	
					columnvalues = (JSONObject) table.get("" + i);
					count = 0;
					Iterator iter = columnvalues.keys();
					while(iter.hasNext()){
						column = iter.next().toString();
						count++;
						if(i == 0 && setStmnt){
							if (firstCol){
								colNames += column;
								colCount += " ? ";
								firstCol = false;
							}
							else{
								colNames += ", " + column;
								colCount +=", ? ";
							}
							if(!(iter.hasNext())){
								insertStmnt = connection.compileStatement("INSERT INTO " + tableName + " (" + colNames + ") VALUES (" + colCount + ");");
								setStmnt = false;
								firstLine = true;
							}
			
						}else{
						
							try{
								insertStmnt.bindString(count, columnvalues.get(column).toString());
								firstLine = false;
							}catch (NullPointerException e) {
								Log.e(TAG, "Nullpointerexception in setMultipleData: " + e.getMessage());
							}
						}
					}
					if(!firstLine){
						insertStmnt.executeInsert();
					}
				}
			}
			
		} catch (JSONException e) {
			Log.e(TAG, "Json exception @ setMultipleData: " + e.getMessage());
		}
		
		return count;
	}
	
	/**
	 * gets complete Database
	 * 
	 */
	private void getCompleteDB(){
		String[] tables = {"floor", "room", "spot" , "radiotype", "device", "sys_device", "command", "controlscript", "link_device_command", "connected_devices"};
		//Map<String, Object> dbMap = new LinkedHashMap<String, Object>();
		JSONObject db = new JSONObject();
		for(String table : tables)
			try {
				db.put(table, getCompleteTable(table));
			} catch (JSONException e) {
				Log.e(TAG, "Json exception: " + e.getMessage());
			}
		
		this.msgContent.setCompleteDbResult(db);
	}
	
	/**
	 * gets a Map of table
	 * @param tablename = table to read
	 * @return Map<Column, Value[]> of table
	 
	private JSONObject getCompleteTable(String tablename){
		JSONObject table = new JSONObject();
		
		String sql ="Select * from " + tablename + " ;";
		result = connection.rawQuery(sql, null);
		int colCount = result.getColumnCount();
		
		for(int i = 0 ; i < colCount ; i++){
			int entry = 0;
			JSONObject values = new JSONObject();
			result.moveToPosition(-1);
			try {
				while(result.moveToNext()){
					if(result.getString(i) != null){
						values.put((""+entry), result.getString(i));
						entry++;
					}
					
				}
				table.put(result.getColumnName(i), values);
			} catch (JSONException e) {
				Log.e(TAG, "Json exception: " + e.getMessage());
			}
		}
		return table;
	}
	
	/**
	 * gets a Map of table
	 * @param tablename = table to read
	 * @return Map<Column, Value[]> of table
	 */
	private JSONObject getCompleteTable(String tablename){
		JSONObject table = new JSONObject();
		
		String sql ="Select * from " + tablename + " ;";
		result = connection.rawQuery(sql, null);
		int colCount = result.getColumnCount();
		try {
			int entry = 0;
			while(result.moveToNext()){
				JSONObject values = new JSONObject();
				for(int i = 0 ; i < colCount ; i++){
					if(result.getString(i) != null){
						values.put(result.getColumnName(i), result.getString(i));
					}
				}
				table.put(""+entry, values);
				entry++;
			}
		} catch (JSONException e) {
			Log.e(TAG, "Json exception @getCompleteTable: " + e.getMessage());
		} catch (NullPointerException e) {
			Log.e(TAG, "Nullpointer exception @getCompleteTable: " + e.getMessage());
		}
		
		return table;
	}
}
